import datetime

import pymysql
from dbutils.pooled_db import PooledDB

from config.config import db


# def Transactional(func):
#     """
#     事务装饰器，统一进行数据库连接的创建和释放，发生异常时进行回滚，减少重复代码
#     使用时在函数前加上@Transactional，并且函数的最后一个参数需要申明为数据库连接，使用时无需传入conn参数
#     以类中普通方法举例（写在UserDao文件中）：
#         class UserDao:
#             @Transactional
#             def test(self, name, conn: MySQLHelper):  # self表示类普通方法
#                 user_id = conn.select_one('SELECT user_id FROM basic_user WHERE user_name = %s', name)[1]['user_id']
#                 print(user_id)
#                 now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
#                 return conn.update('UPDATE basic_user SET gmt_modify = %s WHERE user_id = %s', (now, user_id))
#         #使用时不需要创建MySQLHelper对象
#         print(UserDao().test('admin'))
#
#     :param func:需要包装的函数
#     :return:函数原本的返回值
#     """
#
#     def wrapper(*args, **kwargs):
#         conn = MySQLHelper()
#         try:
#             res = func(*args, conn, **kwargs)
#             conn.commit()
#             return res
#         except Exception as e:
#             print(e)
#             conn.commit('rollback')
#         finally:
#             conn.close()
#
#     return wrapper


class MySQLHelper(object):
    __pool = None

    def __init__(self, creator=db.CREATOR,
                 minCached=db.MIN_CACHED, maxCached=db.MAX_CACHED,
                 maxShared=db.MAX_SHARED, maxConnections=db.MAX_CONNECYIONS,
                 blocking=db.BLOCKING, reset=db.RESET,
                 maxUsage=db.MAX_USAGE, setSession=db.SET_SESSION,
                 host=db.HOST, port=db.PORT, dbName=db.DBNAME,
                 user=db.USER, passwd=db.PASSWORD, charset=db.CHARSET):

        if not self.__pool:
            self.__class__.__pool = PooledDB(creator,
                                             minCached, maxCached,
                                             maxShared, maxConnections,
                                             blocking, reset,
                                             maxUsage, setSession,
                                             host=host, port=port, db=dbName,
                                             user=user, passwd=passwd,
                                             charset=charset,
                                             cursorclass=pymysql.cursors.DictCursor
                                             )
        self._conn = None
        self._cursor = None
        self.__get_conn()

    def __get_conn(self):
        self._conn = self.__pool.connection()
        self._cursor = self._conn.cursor()

    def close(self):
        try:
            self._cursor.close()
            self._conn.close()
        except Exception as e:
            print(e)

    @staticmethod
    def __dict_datetime_obj_to_str(result_dict):
        """把字典里面的dataTime对象转成字符串，使json转换不出错"""
        if result_dict:
            result_replace = {k: v.__str__() for k, v in result_dict.items() if isinstance(v, datetime.datetime)}
            result_dict.update(result_replace)
        return result_dict

    def select_one(self, sql, param=None):
        """查询单个结果"""

        count = self._cursor.execute(sql, param)
        result = self._cursor.fetchone()
        """:type result:dict"""
        result = self.__dict_datetime_obj_to_str(result)
        return count, result

    def select_many(self, sql, param=None):
        """
        查询多个结果
        @param sql: sql语句
        @param param: sql参数
        @return: 结果数量和查询结果集
        """

        count = self._cursor.execute(sql, param)
        result = self._cursor.fetchall()
        """:type result:list"""
        [self.__dict_datetime_obj_to_str(row_dict) for row_dict in result]
        return count, result

    def __get_insert_id(self):
        """
        获取当前连接最后一次插入操作生成的id,如果没有则为０
        """

        self._cursor.execute("SELECT @@IDENTITY AS id")
        result = self._cursor.fetchall()
        return result[0]['id']

    def get_last_id(self):
        return self._cursor.lastrowid

    def insert_one(self, sql, param=None):
        """
        @summary: 向数据表插入一条记录
        @param sql:要插入的ＳＱＬ格式
        @param param:要插入的记录数据tuple/list
        @return: insertId 受影响的行数
        """

        count = self._cursor.execute(sql, param)
        return count

    def insert_many(self, sql, param=None):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的ＳＱＬ格式
        @param param:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """

        count = self._cursor.executemany(sql, param)
        return count

    def update(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: SQL格式及条件，使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """

        count = self._cursor.execute(sql, param)
        return count

    def delete(self, sql, param=None):
        """
        @summary: 删除数据表记录
        @param sql: SQL格式及条件，使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """

        count = self._cursor.execute(sql, param)
        return count

    def commit(self, option='commit'):
        """结束事务"""
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()


# def sPlaceholder(size):
#     """
#     返回size个%s
#     :param size:
#     :return:
#     """
#     return (size * '%s,')[:-1]


def getCurrentTimeStr():
    return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


# _SQL_COUNT = 'SELECT COUNT(*) AS TOTAL_COUNT'


# def pageHelper(connection: MySQLHelper, param: PageDomain, condition, rawSql, preCol, preCnt=_SQL_COUNT):
#     pageNum, pageSize = param.pageNum, param.pageSize
#     sortField, sortOrder = param.sortField, param.sortOrder
#     start, offset = str((pageNum - 1) * pageSize), str(pageSize)
#     sqlCnt = preCnt + rawSql
#     sqlPage = preCol + rawSql
#     if sortField is not None and sortOrder is not None and len(sortField) == len(sortOrder):
#         sqlPage += " order by "
#         for i in range(len(sortField)):
#             sqlPage += sortField[i] + " " + sortOrder[i] + ","
#         sqlPage = sqlPage[:-1]
#     sqlPage += ' LIMIT ' + start + ',' + offset
#     # print(sqlPage)
#     result = (connection.select_one(sqlCnt, condition)[1].get('TOTAL_COUNT'),
#               connection.select_many(sqlPage, condition)[1])
#     return result


if __name__ == "__main__":
    pass
